package com.zhimi.tools.mp3extractor;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;  

public class UniversityImport {

	static void Import(String filename) throws Exception {
		BufferedReader br = new BufferedReader(new FileReader(filename));
		String line = br.readLine();
		StringBuilder sb = new StringBuilder();
		while (line!= null) {
			sb.append(line);
			line = br.readLine();	
		}
		
		Gson gson = new Gson();
		Country china = gson.fromJson(sb.toString(), Country.class);
		br.close();
		
		HttpURLConnectionExample http = new HttpURLConnectionExample();
		http.LoadRegionInfo();
		
		 FileWriter writer=new FileWriter("univers.sql");
		
		http.provsMap.put(32, "香港");
		http.provsMap.put(33, "澳门");
		http.provsMap.put(34, "台湾");

		int level = 1;
		System.out.println(china.name);
		Provs[] provs = china.provs;
		
		for (int i=0;i<provs.length;i++) {
			int provsid = provs[i].id;
			String provsstr = (String) http.provsMap.get(provsid);
			
			for (int j=0;j<provs[i].univs.length;j++) {
				int universityID = provs[i].univs[j].id;
				String universityStr =  provs[i].univs[j].name;
				
				// insert into
				//Class.forName("org.sqlite.JDBC");
				
				//Connection c = DriverManager.getConnection("jdbc:sqlite:voice.db");
				//Connection c = DriverManager.getConnection("jdbc:sqlite:univs.db");
				//System.out.println("Opened database successfully");

				//Statement stmt = c.createStatement();
			//	String provisID = http.provsMap.get(provsid);
//				writer.write("insert into youqu_school(school_name, country_name, "
//						+ "province_name,"
//						+ "school_level, gmt_create) values('" + universityStr +"', '中国','" + provsstr + "', " 
//						+ level+", now());\n");
//				
				
				writer.write("insert into youqu_school(school_name, "
						+ "province_id,"
						+ "school_level) values('" + universityStr.trim() +"'," + provsid + ", " 
						+ level+");\n");
				
//				String sql = "insert into school( level, Country, ProvsID, SchoolID, School) values"
//				+ "(1,\"" + china.name + "\", "+ provsid + ", " + universityID + ",\""
//						+ universityStr + "\")";
//				
//				System.out.println(sql);

			//	stmt.execute(sql);
			}
			
		}
		writer.close();
		System.out.println("Finish");
	
	}
	
	public static void main(String[] args) throws Exception{
		UniversityImport.Import("allunivs.json");
	}

}
